package com.Dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.pojo.Sub;
import com.pojo.User;
public class SubDao {
	private int SubCount;
	private int SubCountx;
	private int SubCounta;
	static {//����MySql������
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {}
	}
	private Connection getConn() throws SQLException {
		String url = "jdbc:mysql://localhost:3306/hi_db" + "?useUnicode=true&characterEncoding=UTF-8";
		String username = "root";
		String password = "15723";
		Connection conn = DriverManager.getConnection(url, username, password);
		return conn;
	}
	
	
	public User findname(String SId) throws SQLException {
		//1.��������
		Connection conn = getConn();//��������ķ���
		//2.����ִ�п�
		Statement st = conn.createStatement();
		//3.��дSQL����
		String sql = "select Uname from user left join cards on cards.UId=user.Uid "
				+ "where cards.SId='"+SId+"'";
		//4��ִ��SQL
		ResultSet rs = st.executeQuery(sql);
		if(rs.next()) {//Ϊtrue����������
			User User = new User(//��ȡ���������User����
				rs.getString("Uname"));//�رս����
			st.close();//�ر�ִ�п�
			conn.close();//�ر�����
			return User;//���ز�ѯ�����û���Ϣ
		}
		else {
			rs.close();//�رս����
			st.close();//�ر�ִ�п�
			conn.close();//�ر�����
			return null;
		}
	}	
	public Sub findcards(String SId) throws SQLException {
		//1.��������
		Connection conn = getConn();//��������ķ���
		//2.����ִ�п�
		Statement st = conn.createStatement();
		//3.��дSQL����
		String sql = "SELECT cards.SId as SId,UId,title,contant,img,theme,time FROM "
				+ "cards left join view on cards.SId=view.SId left join theme_table on cards.Tid=theme_table.Tid"
				+ " WHERE cards.SId='"+SId+"'";
		//4��ִ��SQL
		ResultSet rs = st.executeQuery(sql);
		if(rs.next()) {//Ϊtrue����������
			Sub Sub = new Sub(//��ȡ���������User����
				rs.getString("SId"),
				rs.getString("UId"),
				rs.getString("theme"),
				rs.getString("title"),
				rs.getString("contant"),
				rs.getString("img"),
				rs.getDate("time"));//�رս����
			st.close();//�ر�ִ�п�
			conn.close();//�ر�����
			return Sub;//���ز�ѯ�����û���Ϣ
		}
		else {
			rs.close();//�رս����
			st.close();//�ر�ִ�п�
			conn.close();//�ر�����
			return null;
		}
	}	
public Sub[] findall(String TId) throws SQLException {
	//1.��������
	Connection conn = getConn();//��������ķ���
	//2.����ִ�п�
	Statement st = conn.createStatement();
	//SQL
	String sql = "SELECT cards.SId as SId,UId,title,LEFT(contant,5) as contant,img,theme,time,reply FROM cards left join view on cards.SId=view.SId"
			+ " left join theme_table on cards.Tid=theme_table.Tid"
			+ " where theme = '"+TId+"'  order by cards.time desc";
	ResultSet rs = st.executeQuery(sql);
	Sub[] subArr = new Sub[100];
	this.SubCount = 0;//��ԭͼ������
	while(rs.next()) {
		Sub book = new Sub(
			rs.getString("SId"),
			rs.getString("UId"),
			rs.getString("img"),
			rs.getString("theme"),
			rs.getString("title"),
			rs.getString("contant"),
			rs.getDate("time"),
			rs.getInt("reply"));
		subArr[SubCount] = book;
		SubCount++;
	}
	return subArr;
}
public int getSubCount() {
	return SubCount;
}

public Integer UpdateDelete(String SId) throws SQLException {
	//1.创建连接
	Connection conn = getConn();//调用上面的方法
	//2.创建执行块
	Statement st = conn.createStatement();
	//3.编写SQL代码
	String sql = "delete from cards WHERE "
		+ "SId='"+SId+"'";
	//4、执行SQL
	Integer result = st.executeUpdate(sql);
	//5、关闭执行块
	st.close();
	//6、关闭连接
	conn.close();
	return result;
}

public Sub[] mycards(Integer UId) throws SQLException {
	//1.��������
	Connection conn = getConn();//��������ķ���
	//2.����ִ�п�
	Statement st = conn.createStatement();
	//SQL
	String sql = "SELECT SId,UId,theme,title,time from cards left join theme_table on cards.Tid=theme_table.Tid where UId = '"+UId+"' order by cards.time desc";
	ResultSet rs = st.executeQuery(sql);
	Sub[] subArr = new Sub[100];
	this.SubCounta = 0;//��ԭͼ������
	while(rs.next()) {
		Sub book = new Sub(
			rs.getString("SId"),
			rs.getString("UId"),
			rs.getString("theme"),
			rs.getString("title"),
			rs.getDate("time"));
		subArr[SubCounta] = book;
		SubCounta++;
	}
	return subArr;
}
public int getSubCounta() {
	return SubCounta;
}


}
